' 更新选中表到数据库
' 当修改一个或多个数据模型时 只需要选中修改的模型 然后自动更新到数据库
' 选中pdm中修改的table 多个按住Shift选中 然后执行Customer Command

Option Explicit
ValidationMode = True
InteractiveMode = im_Batch

Dim Model
Set Model = ActiveModel

' 只支持mysql
' 修改为自己的数据库连接信息 
Private Const _
	Dbms = "MySQL 5.0", _
	Server = "192.168.2.10", _
	Port = 3306, _
	Uid = "root", _
	Pwd = "Admin@123456!", _
	Database = "tellyes_dev"

' 数据库连接字符串
Dim ConnString
ConnString = "Driver={MySQL ODBC 5.3 Unicode Driver};" _
	& "Database=" & Database & ";" _
	& "Uid=" & Uid & ";Pwd=" & Pwd & ";" _
    & "Server=" & Server & ";Port=" & Port & ";"

If (Model Is Nothing) Then
    output "There is no current Model!"
ElseIf Not Model.IsKindOf(PdPDM.cls_Model) Then
    output "The current model is not an Physical Data model!"
Else
	' 选中表数量大于1
	If ActiveSelection.Count > 0 Then
		' 设置DBMS
		If Model.DBMS.Name <> Dbms Then
			output "current dbms is " & Model.DBMS.Name & ", will change to " & Dbms
			Model.ChangeDBMS(Dbms)
		End If

		Dim Table
		' 解析每个选中的数据表
		For Each Table In ActiveSelection
			Dim sql
			' 去掉sql中的注释
			sql = RegExp("(/\*.*?\*/)", Table.Preview, "")
			' 去掉换行符
			sql = Replace(sql, vbCrLf, "")
			UpdateTable sql
		Next
	Else
		output "There is no selected table!"
	End If
End If

' 更新表结构过程
Private Sub UpdateTable(ddl)
	Dim Conn
	Set Conn = CreateObject("ADODB.Connection")  
	Conn.Open ConnString
	
	If conn.State = 1 Then
		output "connected to database successful!"
		
		Dim sql
		For Each sql In Split(ddl, ";")
			Conn.Execute(sql)
			On Error Resume Next
		Next
	Else
		output "connected to database failed!"
	End If
	
	Conn.Close
End Sub

' 正则替换
' pattern 正则表达式
' src 源字符串
' dest 正则匹配后替换为dest
Private Function RegExp(pattern, src, dest)
    Dim regEx 
    Set regEx = New RegExp
    regEx.Pattern = pattern
    regEx.IgnoreCase = True
    regEx.Global = True
    RegExp = regEx.Replace(src, dest)
End Function